﻿CREATE PROCEDURE [acms].[Post_GetBySiteUrlAndPath]
	@SiteUrl varchar(50), 
	@Path varchar(255),
	@SiteVersion DateTime
AS

	declare @SiteId int
	select @SiteId = Id From Site_View where Root = @SiteUrl
	
	if(@SiteId is null)
		select @SiteId = SiteId From SiteMappings where MappingName = @SiteUrl

	if(Right(@Path, 1) = '/')
		set @Path = Substring(@Path, 1, Len(@Path) - 1)

	declare @tmp DateTime
	set @tmp = acms.GetPublishDate(@SiteId, @SiteVersion);
	if(@tmp is null and @SiteVersion is not null)
		return
			
	SELECT a.*, b.ParentPath, b.PublishDate as SiteVersion
	From Post_View a
		inner join acms.PostPositions b on a.Id = b.PostId and isNull(b.PublishDate, '9999/12/31') = isNull(@tmp, '9999/12/31')
	Where
		SiteId = @SiteId
		and isNull(ParentPath, '') + Name Collate DATABASE_DEFAULT = @Path

RETURN 0;